﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;

namespace DBToDGML
{
  /// <summary>
  /// Interaction logic for MainWindow.xaml
  /// </summary>
  public partial class MainWindow : Window
  {
    public MainWindow()
    {
      InitializeComponent();
    }

    private void GenerateDGML_Click(object sender, RoutedEventArgs e)
    {
      SqlConnection oSConn = new SqlConnection(SQLConnectionString.Text);
      oSConn.Open();

      StringBuilder oNodeSB = new StringBuilder();
      StringBuilder oLinkSB = new StringBuilder();

      Dictionary<string, bool> oPrimaryKeyLookup;
      Dictionary<string, bool> oForeignKeyLookup;

      ShowFKs(oSConn, oNodeSB, oLinkSB, out oPrimaryKeyLookup, out oForeignKeyLookup);
      ShowTables(oSConn, oNodeSB, oLinkSB, oPrimaryKeyLookup, oForeignKeyLookup);
      
      ResultText.Text = DGMLConstants.DGMLStub.Replace("{Nodes}", oNodeSB.ToString()).Replace("{Links}", oLinkSB.ToString());

      oSConn.Close();
    }

    private static void ShowTables(SqlConnection oSConn, StringBuilder oNodeSB, StringBuilder oLinkSB, Dictionary<string, bool> oPrimaryKeyLookup, Dictionary<string, bool> oForeignKeyLookup)
    {
      Dictionary<string, bool> oTableLookup = new Dictionary<string, bool>();
      Dictionary<string, bool> oSchemaLookup = new Dictionary<string, bool>();

      DataTable oTables = oSConn.GetSchema("Columns");

      DataColumn oTableNameCol = oTables.Columns["TABLE_NAME"];
      DataColumn oColNameCol = oTables.Columns["COLUMN_NAME"];
      DataColumn oSchemaNameCol = oTables.Columns["TABLE_SCHEMA"];
      DataColumn oDataTypeCol = oTables.Columns["DATA_TYPE"];
      DataColumn oMaxLengthCol = oTables.Columns["CHARACTER_MAXIMUM_LENGTH"];
      DataColumn oIsNullableCol = oTables.Columns["IS_NULLABLE"];

      string sTableName, sSchemaName, sFieldName, sFieldDataType, sKeyLookup, sFieldStyle;
      bool bRequired;

      foreach (System.Data.DataRow row in oTables.Rows)
      {
        sTableName = row[oTableNameCol].ToString();
        sSchemaName = row[oSchemaNameCol].ToString();
        sFieldName = row[oColNameCol].ToString();
        sFieldDataType = string.Format("{0}({1})", row[oDataTypeCol], ( string.Compare(row[oMaxLengthCol].ToString(),"-1") == 0 ) ? "MAX" : row[oMaxLengthCol]);
        bRequired = (string.Compare(row[oIsNullableCol].ToString(), "NO") == 0);

        if (oSchemaLookup.ContainsKey(sSchemaName) == false)
        {
          oNodeSB.Append(string.Format(DGMLConstants.DGML_SCHEMANODE, sSchemaName));
          oSchemaLookup.Add(sSchemaName, true);
        }

        if (oTableLookup.ContainsKey(sTableName ) == false)
        {
          oNodeSB.Append(string.Format(DGMLConstants.DGML_TABLENODE, sSchemaName, sTableName ));
          oLinkSB.Append(string.Format(DGMLConstants.DGML_SCHEMA_CONTAINSTABLELINK, sSchemaName, sTableName));
          oTableLookup.Add(sTableName, true);
        }

        sKeyLookup = string.Format("{0},{1},{2}", sSchemaName,sTableName,sFieldName);

        if (oPrimaryKeyLookup.ContainsKey(sKeyLookup) == true)
        {
          sFieldStyle = "Field Primary";
        }
        else if (oForeignKeyLookup.ContainsKey(sKeyLookup) == true)
        {
          sFieldStyle = "Field Foreign";
        }
        else 
        {
          sFieldStyle = bRequired ? "Field" : "Field Optional";
        }

        oNodeSB.Append(string.Format(DGMLConstants.DGML_FIELDNODE, sSchemaName, sTableName, sFieldName, sFieldStyle, sFieldDataType));
        oLinkSB.Append(string.Format(DGMLConstants.DGML_TABLE_CONTAINSFIELDLINK, sSchemaName, sTableName, sFieldName));
      }
    }


    private void ShowFKs(SqlConnection oSConn, StringBuilder oNodeSB, StringBuilder oLinkSB, out Dictionary<string, bool> oPrimaryKeyLookup, out Dictionary<string, bool> oForeignKeyLookup)
    {
      oPrimaryKeyLookup = new Dictionary<string, bool>();
      oForeignKeyLookup = new Dictionary<string, bool>();

      StringBuilder oSB = new  StringBuilder();
      SqlCommand oCmd = new SqlCommand(DGMLConstants.FK_SQL , oSConn);
      SqlDataReader oSReader = oCmd.ExecuteReader(); 

      string sTargetSchema, sTargetTable, sTargetField, sSourceSchema, sSourceTable, sSourceField, sSourceLookup, sTargetLookup;
 
      while (oSReader.Read() )
      {
        sSourceSchema = oSReader[1].ToString();
        sSourceTable = oSReader[2].ToString();
        sSourceField = oSReader[3].ToString();

        sTargetSchema = oSReader[4].ToString();
        sTargetTable = oSReader[5].ToString();
        sTargetField = oSReader[6].ToString();

        sTargetLookup = string.Format("{0},{1},{2}", sTargetSchema, sTargetTable, sTargetField);
        sSourceLookup = string.Format("{0},{1},{2}", sSourceSchema, sSourceTable, sSourceField);

        if (oPrimaryKeyLookup.ContainsKey(sTargetLookup) == false)
        {
          oPrimaryKeyLookup.Add(sTargetLookup, true);
        }

        if (oForeignKeyLookup.ContainsKey(sSourceLookup) == false)
        {
          oForeignKeyLookup.Add(sSourceLookup, true);
        }

        oLinkSB.Append(string.Format(DGMLConstants.DGML_FOREIGNKEY, oSReader[1], oSReader[2], oSReader[3], oSReader[4], oSReader[5], oSReader[6]));
      }

      oSReader.Close();
    }
  }
}

